﻿/******************************************************   
创建者: ljj
创建时间: 2018-01-19
修改:
修改时间: 
功能: 新品下单
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using NHibernateVertica;
using InventoryToPredictAPI.model;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using george.Web.Helper;

namespace InventoryToPredictAPI
{
    [Description("新品下单")]
    public class GoodsNewGoodsOrdersHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：ljj
        /// Create Date：2018-01-19
        [MustLogin]
        [Description("新品下单信息")]
        [ReturnType(typeof(PaperData<IList<GoodsNewGoodsOrdersModel.ShowModel>>))]
        public ApiResult GetGoodsNewGoodsOrdersList([ParamDescription("大类")] string KindID = "",
            [ParamDescription("子项")] string ItemOrSub = "", [ParamDescription("性别")] string SexID = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                if (string.IsNullOrEmpty(companyID))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(extrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var kindIDStr = KindID.GetString();
                var itemOrSubStr = ItemOrSub.GetString();
                var count = ReturnGoodsNewGoodsOrdersCount(extrSource, companyID, kindIDStr, itemOrSubStr, SexID);
                var result = PaperData<GoodsNewGoodsOrdersModel.ShowModel>.Create(count, Size, Page);
                var list = ReturnGoodsNewGoodsOrdersList(extrSource, companyID, kindIDStr, itemOrSubStr, SexID, Page,
                    Size).ToList();
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败!");
            }

        }

        /// <summary>
        /// 返回新品下单信息
        /// </summary>
        /// <param name="ExtrSource"></param>
        /// <param name="CompanyID"></param>
        /// <param name="KindIDStr"></param>
        /// <param name="ItemOrSubStr"></param>
        /// <param name="SexID"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-19
        private IList<GoodsNewGoodsOrdersModel.ShowModel> ReturnGoodsNewGoodsOrdersList(string ExtrSource, string CompanyID, string KindIDStr, string ItemOrSubStr, string SexID, int Page, int Size)
        {
            try
            {
                var sql = $@"SELECT  *
FROM    ( SELECT    a.* ,
                    a.MonthSaleQty + a.PlayReqQty AS TotalQty ,
                    b.ProductDays ,
                    b.StandardSKC ,
                    b.DispalyQty ,
                    kind.KindName ,
                    sex.SexName ,
                    ROW_NUMBER() OVER ( ORDER BY a.KindID , a.SexID , a.ItemOrsub , a.OrderDate, a.ArriverDate, a.Enddate ) AS Row
          FROM      ( SELECT    KindID ,
                                SexID ,
                                ItemOrsub ,
                                OrderDate ,
                                ArriverDate ,
                                Enddate ,
                                PlayReqQty ,
                                SUM(MonthSaleQty) AS MonthSaleQty
                      FROM      ngoods.ngoods_NewGoods_Orders
                      WHERE     1 = 1
                                {(string.IsNullOrEmpty(KindIDStr) ? "" : $"AND KindID IN {KindIDStr}")}
                                {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                {(string.IsNullOrEmpty(ItemOrSubStr) ? "" : $"AND ItemOrsub IN {ItemOrSubStr}")}
                      GROUP BY  KindID ,
                                SexID ,
                                ItemOrsub ,
                                OrderDate ,
                                ArriverDate ,
                                Enddate ,
                                PlayReqQty
                    ) a
                    LEFT JOIN ( SELECT  *
                                FROM    ndw.ndw_KindToItem
                                WHERE   ExtrSource = '{ExtrSource}'
                                        AND CompanyID = '{CompanyID}'
                                        {(string.IsNullOrEmpty(KindIDStr) ? "" : $"AND KindID IN {KindIDStr}")}
                                        {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                        {(string.IsNullOrEmpty(ItemOrSubStr) ? "" : $"AND ItemOrsub IN {ItemOrSubStr}")}
                              ) b ON a.KindID = b.KindID
                                     AND a.SexID = b.SexID
                                     AND a.ItemOrsub = b.ItemOrsub
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{ExtrSource}'
                                        {(string.IsNullOrEmpty(KindIDStr) ? "" : $"AND KindID IN {KindIDStr}")}
                               ) kind ON a.KindID = kind.KindID
                                         AND b.ExtrSource = kind.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Sex
                                 WHERE  ExtrSource = '{ExtrSource}'
                                        {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                               ) sex ON a.SexID = sex.SexID
                                        AND b.ExtrSource = sex.ExtrSource
        ) table1
WHERE   Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page} * {Size}";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<GoodsNewGoodsOrdersModel.ShowModel>.ConvertToModel(dt);
                return list;
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        /// <summary>
        /// 返回新品下单条数
        /// </summary>
        /// <param name="ExtrSource"></param>
        /// <param name="CompanyID"></param>
        /// <param name="KindIDStr"></param>
        /// <param name="ItemOrSubStr"></param>
        /// <param name="SexID"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-19
        private int ReturnGoodsNewGoodsOrdersCount(string ExtrSource, string CompanyID, string KindIDStr, string ItemOrSubStr, string SexID)
        {
            try
            {
                var sql = $@"SELECT  COUNT(1)
FROM    ( SELECT    a.* ,
                    a.MonthSaleQty + a.PlayReqQty AS TotalQty ,
                    b.ProductDays ,
                    b.StandardSKC ,
                    b.DispalyQty ,
                    ROW_NUMBER() OVER ( ORDER BY a.KindID , a.SexID , a.ItemOrsub , a.OrderDate, a.ArriverDate, a.Enddate ) AS Row
          FROM      ( SELECT    KindID ,
                                SexID ,
                                ItemOrsub ,
                                OrderDate ,
                                ArriverDate ,
                                Enddate ,
                                PlayReqQty ,
                                SUM(MonthSaleQty) AS MonthSaleQty
                      FROM      ngoods.ngoods_NewGoods_Orders
                      WHERE     1 = 1
                                {(string.IsNullOrEmpty(KindIDStr) ? "" : $"AND KindID IN {KindIDStr}")}
                                {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                {(string.IsNullOrEmpty(ItemOrSubStr) ? "" : $"AND ItemOrsub IN {ItemOrSubStr}")}
                      GROUP BY  KindID ,
                                SexID ,
                                ItemOrsub ,
                                OrderDate ,
                                ArriverDate ,
                                Enddate ,
                                PlayReqQty
                    ) a
                    LEFT JOIN ( SELECT  *
                                FROM    ndw.ndw_KindToItem
                                WHERE   ExtrSource = '{ExtrSource}'
                                        AND CompanyID = '{CompanyID}'
                                        {(string.IsNullOrEmpty(KindIDStr) ? "" : $"AND KindID IN {KindIDStr}")}
                                        {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                                        {(string.IsNullOrEmpty(ItemOrSubStr) ? "" : $"AND ItemOrsub IN {ItemOrSubStr}")}
                              ) b ON a.KindID = b.KindID
                                     AND a.SexID = b.SexID
                                     AND a.ItemOrsub = b.ItemOrsub
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{ExtrSource}'
                                        {(string.IsNullOrEmpty(KindIDStr) ? "" : $"AND KindID IN {KindIDStr}")}
                               ) kind ON a.KindID = kind.KindID
                                         AND b.ExtrSource = kind.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Sex
                                 WHERE  ExtrSource = '{ExtrSource}'
                                        {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                               ) sex ON a.SexID = sex.SexID
                                        AND b.ExtrSource = sex.ExtrSource
        ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        /// Writer：ljj
        /// Create Date：2018-01-20
        [MustLogin]
        [Description("新品下单辅助信息")]
        [ReturnType(typeof(IList<GoodsNewGoodsOrdersModel.ItemModel>))]
        public ApiResult GetGoodsNewGoodsOrdersItemList([ParamDescription("大类")] string KindID,
            [ParamDescription("子项")] string ItemOrSub, [ParamDescription("性别")] string SexID)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                if (string.IsNullOrEmpty(companyID))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(extrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"SELECT  a.KindID ,
        a.SexID ,
        a.ItemOrsub ,
        CASE a.IsOld
          WHEN 1 THEN '老店'
          ELSE '新店'
        END AS ShopType ,
        a.InfoName ,
        a.month ,
        a.monthDays ,
        a.DaySaleQty ,
        a.MonthSaleQty ,
        a.PlayReqQty ,
        a.ShopNum AS ShopCount ,
        kind.KindName ,
        sex.SexName 
FROM    ngoods.ngoods_NewGoods_Orders a
        INNER JOIN ( SELECT *
                        FROM   nods.nods_erp_SD_Mat_Kind
                        WHERE  ExtrSource = '{extrSource}'
                            {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID = '{KindID}'")}
                    ) kind ON a.KindID = kind.KindID
        INNER JOIN ( SELECT *
                        FROM   nods.nods_erp_SD_Mat_Sex
                        WHERE  ExtrSource = '{extrSource}'
                            {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                    ) sex ON a.SexID = sex.SexID
WHERE   a.KindID = '{KindID}'
        AND a.SexID = '{SexID}'
        AND a.ItemOrsub = '{ItemOrSub}'
ORDER BY a.IsOld ,
        a.InfoName ,
        a.month;";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<GoodsNewGoodsOrdersModel.ItemModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败!");
            }
        }

        /// Writer：ljj
        /// Create Date：2018-01-20
        [MustLogin]
        [Description("计算新品下单")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult CalGoodsNewGoodsOrders()
        {
            try
            {
                var dateTime = DateTime.Now.ToString("yyyy-MM-dd");
                var server = IpAddressHelper.WebConfig("python-server"); //"192.168.10.127";
                var username = IpAddressHelper.WebConfig("python-server-user"); //"bi";
                var port = Convert.ToInt32(IpAddressHelper.WebConfig("python-server-port")); //"22";
                var password = IpAddressHelper.WebConfig("python-server-pass"); //"Km_etl2017";
                var cmdJudge = $"python /home/bi/scripts/python/etl/ndw/SP_ngoods_NewGoods_Orders_Monitor.py";
                var cmd = $"python /home/bi/scripts/python/etl/ndw/SP_ngoods_NewGoods_Orders.py  {dateTime}";
                var resJudge = ConnLinux(server, username, port, password, cmdJudge);
                if (resJudge.Contains("1")) return Error("当前有用户正在计算!");
                var result = ConnLinux(server, username, port, password, cmd);
                return result.Contains("success") ? Success(0) : Error(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("计算失败!");
            }
        }

        /// <summary>
        /// 导出新品下单计算
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-03-13
        public DataTable ExportGoodsNewGoodsOrders(string Keys)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                var sql = $@"SELECT    a.* ,
                    a.MonthSaleQty + a.PlayReqQty AS TotalQty ,
                    b.ProductDays ,
                    b.StandardSKC ,
                    b.DispalyQty ,
                    kind.KindName ,
                    sex.SexName 
          FROM      ( SELECT    KindID ,
                                SexID ,
                                ItemOrsub ,
                                OrderDate ,
                                ArriverDate ,
                                Enddate ,
                                PlayReqQty ,
                                SUM(MonthSaleQty) AS MonthSaleQty
                      FROM      ngoods.ngoods_NewGoods_Orders
                      GROUP BY  KindID ,
                                SexID ,
                                ItemOrsub ,
                                OrderDate ,
                                ArriverDate ,
                                Enddate ,
                                PlayReqQty
                    ) a
                    INNER JOIN ( SELECT  *
                                FROM    ndw.ndw_KindToItem
                                WHERE   ExtrSource = '{extrSource}'
                                        AND CompanyID = '{companyID}'
                              ) b ON a.KindID = b.KindID
                                     AND a.SexID = b.SexID
                                     AND a.ItemOrsub = b.ItemOrsub
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{extrSource}'
                               ) kind ON a.KindID = kind.KindID
                                         AND b.ExtrSource = kind.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Sex
                                 WHERE  ExtrSource = '{extrSource}'
                               ) sex ON a.SexID = sex.SexID
                                        AND b.ExtrSource = sex.ExtrSource
          WHERE     1=1
                    {Keys}
          ORDER BY a.KindID , a.SexID , a.ItemOrsub , a.OrderDate, a.ArriverDate, a.Enddate;";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw(ex);
            }
        }

        /// <summary>
        /// 导出新品下单计算辅助信息
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-03-13
        public DataTable ExportGoodsNewGoodsOrdersItem(string Keys)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                var sql = $@"SELECT  a.KindID ,
        a.SexID ,
        a.ItemOrsub ,
        CASE a.IsOld
          WHEN 1 THEN '老店'
          ELSE '新店'
        END AS ShopType ,
        a.InfoName ,
        a.month ,
        a.monthDays ,
        a.DaySaleQty ,
        a.MonthSaleQty ,
        a.PlayReqQty ,
        a.ShopNum AS ShopCount ,
        kind.KindName ,
        sex.SexName 
FROM    ngoods.ngoods_NewGoods_Orders a
        INNER JOIN ( SELECT *
                        FROM   nods.nods_erp_SD_Mat_Kind
                        WHERE  ExtrSource = '{extrSource}'
                    ) kind ON a.KindID = kind.KindID
        INNER JOIN ( SELECT *
                        FROM   nods.nods_erp_SD_Mat_Sex
                        WHERE  ExtrSource = '{extrSource}'
                    ) sex ON a.SexID = sex.SexID
WHERE   1=1
        {Keys}
ORDER BY a.KindID , 
        a.SexID ,
        a.ItemOrsub ,
        a.IsOld ,
        a.InfoName ,
        a.month;";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw(ex);
            }
        }
    }
}
